package com.sajus.web.billing.admin.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Time;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import javax.naming.NamingException;

import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.opensymphony.xwork2.ActionSupport;
import com.sajus.billing.client.delegate.BusinessDelegateFactory;
import com.sajus.billing.client.dto.AccountAttributeDTO;
import com.sajus.billing.client.dto.AccountDTO;
import com.sajus.billing.client.dto.OfferingDTO;
import com.sajus.billing.client.dto.PromotionDTO;
import com.sajus.billing.client.dto.ServiceAttributeDTO;
import com.sajus.billing.client.dto.ServiceDTO;
import com.sajus.billing.client.dto.ServiceTypeDTO;

public class ImportToolAction  extends ActionSupport {

	private File fileUpload;
	private String fileUploadContentType;
	private String fileUploadFileName;
	
	private final String USAGE = "Usage";
	private final int USAGE_ID = 2;
	private final int RECURRING_ID = 1;
	
	private final int ACCOUNT_VAR = 1;
	private final int ACCOUNT_DOM = 2;
	private final int ACCOUNT_CUST = 3;
	private final int ACCOUNT_DEMO = 4;

	public String getFileUploadContentType() {
		return fileUploadContentType;
	}

	public void setFileUploadContentType(String fileUploadContentType) {
		this.fileUploadContentType = fileUploadContentType;
	}

	public String getFileUploadFileName() {
		return fileUploadFileName;
	}

	public void setFileUploadFileName(String fileUploadFileName) {
		this.fileUploadFileName = fileUploadFileName;
	}

	public File getFileUpload() {
		return fileUpload;
	}

	public void setFileUpload(File fileUpload) {
		this.fileUpload = fileUpload;
	}

	
	public String execute() throws Exception {

		if ( fileUpload != null ){

			try {

				File fileToCreate = new File( fileUploadFileName );
				FileUtils.copyFile(fileUpload, fileToCreate );
				
				FileInputStream file = new FileInputStream( fileUploadFileName );

				XSSFWorkbook workbook = new XSSFWorkbook (file);
				
				XSSFSheet serviceTypeSheet = workbook.getSheet("Service Types");
				XSSFSheet serviceOfferingsSheet = workbook.getSheet("Service Offerings");
				XSSFSheet promotionsSheet = workbook.getSheet("Promotions");
				XSSFSheet accountsSheet = workbook.getSheet("Accounts");
				XSSFSheet serviceMappingSheet = workbook.getSheet("Service Mapping");
				
				System.out.println("Loading the Service Types");
				
				if ( serviceTypeSheet != null ){
					loadServiceTypes( serviceTypeSheet );
				}
				if ( serviceOfferingsSheet != null ){
					loadServiceOfferings( serviceOfferingsSheet );
				}
				if ( promotionsSheet != null ){
					loadPromotions( promotionsSheet );
				}
				if ( accountsSheet != null ){
					loadAccounts( accountsSheet );
				}
				if ( serviceMappingSheet != null ){
					loadServices( serviceMappingSheet );
				}
				
			} catch ( Exception e) {
				e.printStackTrace();
			}

			return SUCCESS;

		}
		return SUCCESS;
	}

	private void loadServices(XSSFSheet serviceTypeSheet) throws NamingException {
		
		Iterator<Row> row = serviceTypeSheet.iterator();
		int serSize = row.next().getLastCellNum();  
		int attSize = row.next().getLastCellNum();
		serSize = Math.max( serSize , attSize );
		while ( row.hasNext() ){
			
			ServiceDTO service = new ServiceDTO();
			ServiceAttributeDTO servAtt = new ServiceAttributeDTO();
			ArrayList<ServiceAttributeDTO> attList = new ArrayList<ServiceAttributeDTO>();
			Row curRow = row.next();
			int index = 0;
			while ( index < serSize ){
				Cell curCell = curRow.getCell( index, Row.RETURN_NULL_AND_BLANK );
				try {
					if ( curCell == null || curCell.getCellType() == Cell.CELL_TYPE_BLANK ){
						System.out.println("empty string found ");
						index++;
						continue;
					}
				} catch (Exception e ){
				}
				switch ( index ) {
				case 0:
					Double acctID = curCell.getNumericCellValue();
					int acctId = acctID.intValue();
					AccountDTO acct = BusinessDelegateFactory.getInstance().getAccountManager().getAccount( acctId );
					if ( acct != null ){
						service.setAccount( acct );
					}
					break;
				case 1:
					String offerName = curCell.getStringCellValue();
					List<OfferingDTO> offList = BusinessDelegateFactory.getInstance().getServiceTypeManager().getOfferings();
					for ( OfferingDTO off : offList ){
						if ( offerName.equals( off.getServiceType().getDescription() ) 
								|| offerName.equals( off.getDescription() ) ) {
							service.setOffering( off );
							if ( off.getServiceType().getType().intValue() == USAGE_ID ){
								servAtt = new ServiceAttributeDTO();
								servAtt.setName("currentUsage");
								servAtt.setValue( "0" );
								servAtt.setService( service );
								attList.add( servAtt );
								break;
							}
						}
					}
					break;
				case 2:
					if ( curCell.getStringCellValue().equals("active") ){
						service.setStatus( 1 );
					} else {
						service.setStatus( 0 );
					}
					break;
				case 3:
					service.setSubscriptionDate( curCell.getDateCellValue() );
					break;
				case 4:
					service.setActivationDate( curCell.getDateCellValue() );
					break;
				case 5:
					service.setBaseRate( curCell.getNumericCellValue() );
					break;
				case 6:
					service.setLimit( curCell.getNumericCellValue() );
					break;
				case 7:
					service.setStep1Unit( curCell.getNumericCellValue() );
					break;
				case 8:
					service.setStep1Rate( curCell.getNumericCellValue() );
					break;
				case 9:
					service.setStep2Unit( curCell.getNumericCellValue() );
					break;
				case 10:
					service.setStep2Rate( curCell.getNumericCellValue() );
					break;
				case 11:
					service.setMinUnit( curCell.getNumericCellValue() );
					break;
				case 12:
					service.setMaxUnit( curCell.getNumericCellValue() );
					break;
				case 13:
					service.setMinRate( curCell.getNumericCellValue() );
					break;
				case 14:
					service.setMaxRate( curCell.getNumericCellValue() );
					break;
				case 15:
					service.setPreRule( curCell.getStringCellValue() );
					break;
				case 16:
					service.setPostRule( curCell.getStringCellValue() );
					break;
				case 17:
					servAtt = new ServiceAttributeDTO();
					servAtt.setName( "DeviceESN" );
					servAtt.setService( service );
					try {
						Double deviceESN = curCell.getNumericCellValue();
						servAtt.setValue( deviceESN.intValue() + "" );
					} catch ( IllegalStateException e  ){
						servAtt.setValue( curCell.getStringCellValue() );
					}
					
					attList.add( servAtt );
					break;
				case 18:
					servAtt = new ServiceAttributeDTO();
					servAtt.setName( "DeviceID" );
					servAtt.setService( service );
					try{
						Double deviceID = curCell.getNumericCellValue();
						servAtt.setValue( deviceID.intValue() + "" );
					} catch ( Exception e ){
						servAtt.setValue( curCell.getStringCellValue() );
					}
					attList.add( servAtt );
					break;
				case 19:
					servAtt = new ServiceAttributeDTO();
					servAtt.setName( "ChannelID" );
					servAtt.setService( service );
					try{
						Double channelID = curCell.getNumericCellValue();
						servAtt.setValue( channelID.intValue() + "" );
					} catch ( Exception e ){
						servAtt.setValue( curCell.getStringCellValue() );
					}
					attList.add( servAtt );
					break;
				case 20:
					servAtt = new ServiceAttributeDTO();
					servAtt.setName( "Gateway" );
					servAtt.setService( service );
					servAtt.setValue( curCell.getStringCellValue() );
					attList.add( servAtt );
					break;
				case 21:
					servAtt = new ServiceAttributeDTO();
					servAtt.setName( "Geocoding" );
					servAtt.setService( service );
					servAtt.setValue( curCell.getStringCellValue() );
					attList.add( servAtt );
					break;
				case 22:
					servAtt = new ServiceAttributeDTO();
					servAtt.setName( "Mobile" );
					servAtt.setService( service );
					servAtt.setValue( curCell.getStringCellValue() );
					attList.add( servAtt );
					break;
				case 23:
					servAtt = new ServiceAttributeDTO();
					servAtt.setName( "Mapping" );
					servAtt.setService( service );
					servAtt.setValue( curCell.getStringCellValue() );
					attList.add( servAtt );
					break;
				case 24:
					servAtt = new ServiceAttributeDTO();
					servAtt.setName( "Reporting" );
					servAtt.setService( service );
					servAtt.setValue( curCell.getStringCellValue() );
					attList.add( servAtt );
					break;
				}
				index++;
			}
			if ( service != null ){
				service.setServiceAttributes( attList );
			}
			BusinessDelegateFactory.getInstance().getServiceTypeManager().saveService( service );
		}
		
	}

	private void loadAccounts(XSSFSheet serviceTypeSheet) throws NamingException {
		
		AccountAttributeDTO acctAtt = new AccountAttributeDTO();
		
		Iterator<Row> row = serviceTypeSheet.iterator();
		
		int acctSize = row.next().getLastCellNum();  
		int attSize = row.next().getLastCellNum();
		while ( row.hasNext() ){
			Set<AccountAttributeDTO> attList = new HashSet<AccountAttributeDTO>();
			AccountDTO account = new AccountDTO();
			Row curRow = row.next();
			int index = 0;
			while ( index < acctSize ){
				Cell curCell = curRow.getCell( index, Row.CREATE_NULL_AS_BLANK );
				try {
					if ( curCell == null || curCell.getCellType() == Cell.CELL_TYPE_BLANK ){
						System.out.println("empty string found ");
						index++;
						continue;
					}
				} catch (Exception e ){
				}
				switch ( index ) {
				case 0:
					System.out.println(" Account ID " + curCell.getNumericCellValue() );
					break;
				case 1:
					account.setCompany( curCell.getStringCellValue() );
					break;
				case 2:
					account.setEmail( curCell.getStringCellValue() );
					break;
				case 3:
					account.setPhone( curCell.getStringCellValue() );
					break;
				case 4:
					account.setStreetAddress( curCell.getStringCellValue() );
					break;
				case 5:
					account.setStreetAddress2( curCell.getStringCellValue() );
					break;
				case 6:
					account.setCountry( curCell.getStringCellValue() );
					break;
				case 7:
					account.setProvince( curCell.getStringCellValue() );
					break;
				case 8:
					account.setCity( curCell.getStringCellValue() );
					break;
				case 9:
					account.setFirstName( curCell.getStringCellValue() );
					break;
				case 10:
					account.setLastName( curCell.getStringCellValue() );
					break;
				case 11:
					account.setBillingPeriodType( curCell.getStringCellValue() );
					break;
				case 12:
					Double cellValue = curCell.getNumericCellValue();
					account.setBillingPeriodStartDay( cellValue.intValue() );
					break;
				case 13:
					account.setBillingTime( new Time(2, 0, 0) );
					break;
				case 14:
					Double cellValueBillingDay = curCell.getNumericCellValue();
					account.setBillingDay( cellValueBillingDay.intValue() );
					break;
				case 15:
					account.setBillingPeriodStartTime( new Time(0,0,0) );
					break;
				case 16:
					account.setNextBillingDate( curCell.getDateCellValue() );
					break;
				case 17:
					if ( "active".equals( curCell.getStringCellValue() ) ){
						account.setStatus( 1 );
					} else {
						account.setStatus( 0 );
					}
					break;
				case 18:
					String type = curCell.getStringCellValue();
					if ( "VAR".equals( type ) ){
						account.setType( ACCOUNT_VAR );
					}
					if ( "DOMAIN".equals( type ) ){
						account.setType( ACCOUNT_DOM );
					}
					if ( "DEMO".equals( type ) ){
						account.setType( ACCOUNT_DEMO );
					}
					if ( "CUST".equals( type ) ){
						account.setType( ACCOUNT_CUST );
					}
					break;
				case 19:
					Double parentID = curCell.getNumericCellValue();
					int parent = parentID.intValue();
					if ( parent != 0) {
						AccountDTO parAcc = BusinessDelegateFactory.getInstance().getAccountManager().getAccount( parent );
						if ( parAcc != null ){
							account.setParent( parAcc );
						}
					}
					break;
				case 20:
					acctAtt = new AccountAttributeDTO();
					acctAtt.setName( "New" );
					acctAtt.setAccount( account );
					acctAtt.setValue( curCell.getStringCellValue() );
					attList.add( acctAtt );
					break;
				case 21:
					acctAtt = new AccountAttributeDTO();
					acctAtt.setName( "ResellerId" );
					acctAtt.setAccount( account );
					acctAtt.setValue( curCell.getStringCellValue() );
					attList.add( acctAtt );
					break;
				case 22:
					acctAtt = new AccountAttributeDTO();
					acctAtt.setName( "DomainId" );
					acctAtt.setAccount( account );
					Double domainID = curCell.getNumericCellValue();
					acctAtt.setValue( domainID.intValue()  + "" );
					attList.add( acctAtt );
					break;
				case 23:
					acctAtt = new AccountAttributeDTO();
					acctAtt.setName( "CustomerId" );
					acctAtt.setAccount( account );
					Double customerId = curCell.getNumericCellValue();
					acctAtt.setValue( customerId.intValue() + "" );
					attList.add( acctAtt );
					break;
				}
				index++;
			}
			if ( account != null ){
				account.setAccountAttributes( attList );
			}
			BusinessDelegateFactory.getInstance().getAccountManager().saveAccount( account );
		}
		
	}

	private void loadPromotions(XSSFSheet serviceTypeSheet) throws NamingException {
		Iterator<Row> row = serviceTypeSheet.iterator();
		row.next();
		row.next();
		while ( row.hasNext() ){
			PromotionDTO promotion = new PromotionDTO();
			Row curRow = row.next();
			int index = 0;
			for ( Cell curCell : curRow ){
				try {
					if ( "".equals( curCell.getStringCellValue() ) ){
						System.out.println("Problem with import an empty string found ");
						index++;
						continue;
					}
					
					if ( curCell.getCellType() == Cell.CELL_TYPE_BLANK ){
						System.out.println("Problem with import an empty string found ");
						index++;
						continue;
					}
				} catch (Exception e ){
				}
				switch ( index ) {
				case 0:
					promotion.setName( curCell.getStringCellValue() );
					break;
				case 1:
					promotion.setDescription( curCell.getStringCellValue() );
					break;
				case 2:
					promotion.setDuration( Integer.parseInt( curCell.getStringCellValue() ) );
					break;
				case 3:
					promotion.setStartDate( curCell.getDateCellValue() );
					break;
				case 4:
					promotion.setEndDate( curCell.getDateCellValue() );
					break;
				}
				index++;
			}
			BusinessDelegateFactory.getInstance().getServiceTypeManager().savePromotion( promotion );
		}
	}

	private void loadServiceOfferings(XSSFSheet serviceTypeSheet) throws NamingException {
		Iterator<Row> row = serviceTypeSheet.iterator();
		row.next();
		while ( row.hasNext() ){
			OfferingDTO offering = new OfferingDTO();
			Row curRow = row.next();
			int index = 0;
			for ( Cell curCell : curRow ){
				try {
					if ( "".equals( curCell.getStringCellValue() ) ){
						System.out.println("Problem with import an empty string found ");
						index++;
						continue;
					}
					
					if ( curCell.getCellType() == Cell.CELL_TYPE_BLANK ){
						System.out.println("Problem with import an empty string found ");
						index++;
						continue;
					}
				} catch (Exception e ){
				}
				switch ( index ) {
				case 0:
					offering.setDescription( curCell.getStringCellValue() );
					break;
				case 1:
					List<ServiceTypeDTO> serTypes = BusinessDelegateFactory.getInstance().getServiceTypeManager().getServiceTypes();
					for ( ServiceTypeDTO ser : serTypes ){
						if ( ser.getDescription().equals( curCell.getStringCellValue() ) ){
							offering.setServiceType( ser );
						}
					}
					break;
				case 2:
					offering.setBaseRate( curCell.getNumericCellValue() );
					break;
				case 3:
					offering.setLimit( curCell.getNumericCellValue()  );
					break;
				case 4:
					offering.setStep1Unit(  curCell.getNumericCellValue()  );
					break;
				case 5:
					offering.setStep1Rate(  curCell.getNumericCellValue()  );
					break;
				case 6:
					offering.setStep2Unit(  curCell.getNumericCellValue()  );
					break;
				case 7:
					offering.setStep2Rate(  curCell.getNumericCellValue()  );
					break;
				case 8:
					offering.setMinRate(  curCell.getNumericCellValue()  );
					break;
				case 9:
					offering.setMinUnit(  curCell.getNumericCellValue()  );
					break;
				case 10:
					offering.setMaxRate(  curCell.getNumericCellValue()  );
					break;
				case 11:
					offering.setMaxUnit(  curCell.getNumericCellValue()  );
					break;
				case 12:
					offering.setPreRule( curCell.getStringCellValue() );
					break;
				case 13:
					offering.setPostRule( curCell.getStringCellValue() );
					break;
				}
				index++;
			}
			BusinessDelegateFactory.getInstance().getServiceTypeManager().saveOffering( offering );
		}
	}

	private void loadServiceTypes(XSSFSheet serviceTypeSheet) throws NamingException {
		Iterator<Row> row = serviceTypeSheet.iterator();
		row.next();
		while ( row.hasNext() ){
			ServiceTypeDTO serviceType = new ServiceTypeDTO();
			Row curRow = row.next();
			int index = 0;
			for ( Cell curCell : curRow ){
				try {
					if ( "".equals( curCell.getStringCellValue() ) ){
						System.out.println("Problem with import an empty string found ");
						index++;
						continue;
					}
					
					if ( curCell.getCellType() == Cell.CELL_TYPE_BLANK ){
						System.out.println("Problem with import an empty string found ");
						index++;
						continue;
					}
				} catch (Exception e ){
				}
				switch ( index ) {
				case 0:
					serviceType.setDescription( curCell.getStringCellValue() );
					break;
				case 1:
					String type = curCell.getStringCellValue();
					if ( USAGE.equals( type ) ){
						serviceType.setType( new Integer( USAGE_ID ) );
					} else {
						serviceType.setType( new Integer( RECURRING_ID ) );
					}
					break;
				case 2:
					serviceType.setUnit( curCell.getStringCellValue() );
					break;
				case 3:
					serviceType.setServicetable( curCell.getStringCellValue() );
					break;
				case 4:
					serviceType.setServicecolumn( curCell.getStringCellValue() );
					break;
				case 5:
					serviceType.setIdentifier( curCell.getStringCellValue() );
					break;
				}
				System.out.println("Current Value " + curCell.getStringCellValue() );
				index++;
			}
			BusinessDelegateFactory.getInstance().getServiceTypeManager().saveServiceType( serviceType );
		}
	}
}
